package com.qf.mybatisplus.utils.excel;

import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description excel导出工具类  使用poi-ooxml 依赖的版本是3.9 其他版本会报错
* @Param
* @Author 12136
* @Date 14:34 2021/11/30
* @return
**/
public class ExportExcelUtil {

/**
* @Description  将excel导出到指定位置 默认是 .xlsx格式  如果需要导出.xls格式的 将下面注释调的代码替换即可
* @Param [title sheet标题, headers 表头, dossierList 数据list, out 输出流, pattern 日期格式]
* @Author ZDD
* @Date 14:32 2021/11/18
* @return java.util.Map<java.lang.String,java.lang.Object>
**/
//    public static Map<String,Object>  exportMessageExcelFile(String title, String[] headers,List dossierList, OutputStream out, String pattern)
//    {
//        boolean flag = true;
//        Map<String,Object> map = new HashMap<String,Object>();
//        StringBuffer messageFile = new StringBuffer();
//        // 声明一个工作薄 生成后缀名为 .xlsx格式的表格
//        XSSFWorkbook workbook = new XSSFWorkbook();
//        // 如果需要生成后缀名为 .xls格式的用这个对象
////        HSSFWorkbook workbook = new HSSFWorkbook();
//        // 生成一个表格
//        XSSFSheet sheet = workbook.createSheet(title);
////        HSSFSheet sheet = workbook.createSheet(title);
//        // 设置表格默认列宽度为15个字节
//        sheet.setDefaultColumnWidth((short) 15);
//        // 生成一个样式
//        XSSFCellStyle style = workbook.createCellStyle();
////        HSSFCellStyle style = workbook.createCellStyle();
//        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        //产生表格标题行
//        XSSFRow row = sheet.createRow(0);
////        HSSFRow row = sheet.createRow(0);
//        for (short i = 0; i < headers.length; i++) {
//            XSSFCell cell = row.createCell(i);
////            HSSFCell cell = row.createCell(i);
//            cell.setCellStyle(style);
//
////            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
//            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
//            cell.setCellValue(text);
//        }
//        for(int i = 0; i< dossierList.size();i++){
//
//            Object[] obj =  (Object[]) dossierList.get(i);
//            row = sheet.createRow(i+1);
//            for (int j = 0; j < obj.length; j++) { // 5
//
//                XSSFCell cell = row.createCell(j);
////                HSSFCell cell = row.createCell(j);
//                cell.setCellStyle(style);
//                if(j==0){
//
//                    cell.setCellValue(i+1);
//                }else {
//                    // 如果导出的列第一列需要递增的序号，则取[j-1]，否则取[j]
//                    cell.setCellValue(obj[j-1]==null?"":obj[j-1].toString());
//                }
//            }
//        }
//        map.put("messageFile", messageFile.toString().endsWith(",")?messageFile.toString().substring(0, messageFile.toString().length()-1):messageFile.toString());
//        try {
//            workbook.write(out);
//        } catch (IOException e) {
//            e.printStackTrace();
//            flag = false;
//        }finally {
//            //清理资源
//            try {
//                if(out != null)
//                {
//                    out.close();
//                }
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
//        map.put("flag", flag);
//        return map;
//    }
//


    /**
    * @Description  web浏览器访问时在浏览器下载此表格
    * @Param [title sheet标题, headers 表头, dataList 数据list, response 响应对象]
    * @Author ZDD
    * @Date 14:33 2021/11/18
    * @return void
    **/
//    public static void downloadExcelFile(String title,String[] headers,List<Object[]> dataList, HttpServletResponse response) {
//        try {
//            ByteArrayOutputStream os = new ByteArrayOutputStream();
//            ExportExcelUtil.exportMessageExcelFile(title, headers, dataList, os, "yyyy-MM-dd");
//            byte[] content = os.toByteArray();
//            InputStream is = new ByteArrayInputStream(content);
//            // 设置response参数，可以打开下载页面
//            response.reset();
//
//            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
//            response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xlsx").getBytes("utf-8"), "ISO8859-1"));
//            response.setContentLength(content.length);
//            response.addHeader("Access-Control-Allow-Origin", "*");
//            ServletOutputStream outputStream = response.getOutputStream();
//            BufferedInputStream bis = new BufferedInputStream(is);
//            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
//            byte[] buff = new byte[8192];
//            int bytesRead;
//            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
//                bos.write(buff, 0, bytesRead);
//
//            }
//            bis.close();
//            bos.close();
//            outputStream.flush();
//            outputStream.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

//    public static void main(String[] args) {
//
//        String[] headers = { "学号","姓名","爱好","爱好","爱好","爱好","爱好","爱好"};
//        List<Object[]> dataset = new ArrayList<Object[]>();
//        dataset.add(new Object[]{"1","李四","唱歌","唱歌","唱歌","唱歌","唱歌","唱歌"});
//        dataset.add(new Object[]{"2","李四","唱歌","唱歌","唱歌","唱歌","唱歌","唱歌"});
//        dataset.add(new Object[]{"3","李四","唱歌","唱歌","唱歌","唱歌","唱歌","唱歌"});
//        dataset.add(new Object[]{"4","李四","唱歌","唱歌","唱歌","唱歌","唱歌","唱歌"});
//        dataset.add(new Object[]{"5","李四","唱歌","唱歌","唱歌","唱歌","唱歌","唱歌"});
//        try {
//            OutputStream out = new FileOutputStream("C:\\桌面\\用户信息表.xlsx");
//            ExportExcelUtil.exportMessageExcelFile("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
//        } catch (FileNotFoundException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
//    }

}
